<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
             
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('B1:N1');
$activeSheet->setCellValue('B1', 'Accounting Redemption Detail Report - On Product');
$activeSheet->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('B1')->getFont()->setSize(24);
$activeSheet->getStyle('B1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 6;
$activeSheet->setCellValue('A'.$index, 'No');
$activeSheet->setCellValue('B'.$index, 'Item');
$activeSheet->setCellValue('C'.$index, 'Quantity');
$activeSheet->setCellValue('D'.$index, 'Redemption Code');
$activeSheet->setCellValue('E'.$index, 'Date Redeem');
$activeSheet->setCellValue('F'.$index, 'Member ID');
$activeSheet->setCellValue('G'.$index, 'Name');
$activeSheet->setCellValue('H'.$index, 'Point Redemption');
$activeSheet->setCellValue('I'.$index, 'Cash Redemption');
$activeSheet->setCellValue('J'.$index, 'Retail Pricing');
$activeSheet->setCellValue('K'.$index, 'Cost pricing');
$activeSheet->setCellValue('L'.$index, 'Outlet');
$activeSheet->setCellValue('M'.$index, 'Percent');
$activeSheet->setCellValue('N'.$index, 'Charge Cost');

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J','K','L','M','N');
$fill = $activeSheet->getStyle('A6:N6')->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB("cc99ff");

foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, 6, 5);
    }
	else if($item == 'B')
    {
        setBackgroundColor($activeSheet, $item, 6, 30);
    }
	else if($item == 'C')
    {
        setBackgroundColor($activeSheet, $item, 6, 12);
    }
	
	else if($item == 'E')
    {
        setBackgroundColor($activeSheet, $item, 6, 15);
    }
	else if($item == 'F')
    {
        setBackgroundColor($activeSheet, $item, 6, 14);
    }
	else if($item == 'G')
    {
        setBackgroundColor($activeSheet, $item, 6, 15);
    }
   
    else if($item == 'L')
    {
        setBackgroundColor($activeSheet, $item, 6, 30);
    }
    else if($item == 'J')
    {
        setBackgroundColor($activeSheet, $item, 6, 10);   
    }
	else if($item == 'K')
    {
        setBackgroundColor($activeSheet, $item, 6, 15);   
    }
	else {
		setBackgroundColor($activeSheet, $item, 6, 20);
	}
}

// End init excel

/******** Query Data ********/  
$db = JFactory::getDbo();
$conditions = array();
$conditions['dateFrom'] = strval($_GET['df']);
$conditions['dateTo'] = strval($_GET['dt']);
$conditions['status'] = strval($_GET['s']);
$conditions['products'] = strval($_GET['p']);
$conditions['outlet'] = strval($_GET['o']);

$data = reportProvider::redemptionreportproduct($conditions, $db, intval($_GET['l']));

//echo '<pre>';print_r($data);
if(!empty($data))
{
    $activeSheet->setCellValue('B3', 'From: ');
	$activeSheet->setCellValue('B4', 'To: ');
	$activeSheet->setCellValue('C3', $conditions['dateFrom']);
	$activeSheet->setCellValue('C4', $conditions['dateTo']);
	$activeSheet->setCellValue('F3', 'Status');	
    $activeSheet->setCellValue('G3', 'Redeemed');
    
    /*********** BIND TO EXCEL ***********/
    $index = 7;
    $data = $data['items'];
    $size = count($data);
    
    for($i=0; $i<$size; $i++)
    {
		//echo $data[$i]->code."<br />";die;
		$conditions['code'] = strval($data[$i]->code);
		//echo $conditions['code']."<br />";die;
		$tr = PointProvider::getTransactionByCode($db, $conditions);
		$total = 0;
		foreach($tr as $its){
			$total += $its->point;
		}
		//echo '<pre>';print_r($tr);die;
		$arr = explode(",",$conditions['outlet']);	

			for($j=0;$j<count($tr);$j++){
				if(!empty($arr)&&(in_array($tr[$j]->outlet,$arr))):
					$activeSheet->setCellValue('A'.$index, $index-6); 
					$product_arr = unserialize($data[$i]->product);
					$product = $product_arr['en-GB'];
					
					$activeSheet->setCellValue('B'.$index, $product);    
					$activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
					
					$activeSheet->setCellValue('C'.$index, $data[$i]->quantity);                
					
					$activeSheet->setCellValue('D'.$index, $data[$i]->code);                
					$activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
						 
					$activeSheet->setCellValue('E'.$index, date('d-m-Y',strtotime($data[$i]->exchange_date)));                
					$activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
					
					$activeSheet->setCellValue('F'.$index, "8800".$data[$i]->member_id."01");
					$objPHPExcel->getActiveSheet()->getStyle('F'.$index)->getNumberFormat()->setFormatCode("0");
					
					$activeSheet->setCellValue('G'.$index,$data[$i]->l_name." ".$data[$i]->f_name);
					$activeSheet->setCellValue('H'.$index,$data[$i]->point);
					$objPHPExcel->getActiveSheet()->getStyle('H'.$index)->getNumberFormat()->setFormatCode("#,##0");
					
					$activeSheet->setCellValue('I'.$index,$data[$i]->cash);
					$objPHPExcel->getActiveSheet()->getStyle('I'.$index)->getNumberFormat()->setFormatCode("#,##0");
					
					$activeSheet->setCellValue('J'.$index, $data[$i]->price);   
					$activeSheet->getStyle('J'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('J'.$index)->getNumberFormat()->setFormatCode("#,##0");
					
					$activeSheet->setCellValue('K'.$index,$data[$i]->cost_pricing);
					$activeSheet->getStyle('K'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('K'.$index)->getNumberFormat()->setFormatCode("#,##0");
					if($tr[$j]->name != ''){
						$outName =  $tr[$j]->name;
					} 
					else if(($tr[$j]->type == 9) && ($tr[$j]->transaction == 0)){
						$outName =  "WMCRewards - Start Point";
					}
					else if(($tr[$j]->type == 0) &&($tr[$j]->transaction == 0)){
						$outName =  "WMCRewards";
					}
					else {
						$db = JFactory::getDbo();
						if(empty($row->name)){
							$sql = "SELECT des,MIN(id) FROM #__wrd_point WHERE transaction=".$tr[$j]->transaction;
							$db->setQuery($sql);
							$des = $db->loadObjectList();
							$outName =  "WMC GROUP - ".$des[0]->des;
						}
					}

					$activeSheet->setCellValue('L'.$index,$outName);
					$activeSheet->getStyle('L'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$activeSheet->setCellValue('M'.$index,($tr[$j]->point/$total));
					$objPHPExcel->getActiveSheet()->getStyle('M'.$index)->getNumberFormat()->setFormatCode("0.00%");
					$activeSheet->getStyle('M'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$activeSheet->setCellValue('N'.$index,$data[$i]->quantity * $data[$i]->cost_pricing*($tr[$j]->point/$total));
					$activeSheet->getStyle('N'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('N'.$index)->getNumberFormat()->setFormatCode("#,##0");
					$index++;
				elseif(empty($arr[0])) :
				
					$activeSheet->setCellValue('A'.$index, $index-7); 
				
					$product_arr = unserialize($data[$i]->product);
					$product = $product_arr['en-GB'];
				
					$activeSheet->setCellValue('B'.$index, $product);    
					$activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
					
					$activeSheet->setCellValue('C'.$index, $data[$i]->quantity);                
					
					$activeSheet->setCellValue('D'.$index, $data[$i]->code);                
					$activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
						 
					$activeSheet->setCellValue('E'.$index, date('d-m-Y',strtotime($data[$i]->exchange_date)));                
					$activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
					
					$activeSheet->setCellValue('F'.$index, "8800".$data[$i]->member_id."01");
					$objPHPExcel->getActiveSheet()->getStyle('F'.$index)->getNumberFormat()->setFormatCode("0");
					
					$activeSheet->setCellValue('G'.$index,$data[$i]->l_name." ".$data[$i]->f_name);
					$activeSheet->setCellValue('H'.$index,$data[$i]->point);
					$activeSheet->setCellValue('I'.$index,$data[$i]->cash);
					
					$activeSheet->setCellValue('J'.$index, $data[$i]->price);   
					$activeSheet->getStyle('J'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('J'.$index)->getNumberFormat()->setFormatCode("#,##0");		 
					$activeSheet->setCellValue('K'.$index,$data[$i]->cost_pricing);
					$activeSheet->getStyle('K'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('K'.$index)->getNumberFormat()->setFormatCode("#,##0");
					if($tr[$j]->name != ''){
						$outName =  $tr[$j]->name;
					} 
					else if(($tr[$j]->type == 9) && ($tr[$j]->transaction == 0)){
						$outName =  "WMCRewards - Start Point";
					}
					else if(($tr[$j]->type == 0) &&($tr[$j]->transaction == 0)){
						$outName =  "WMCRewards";
					}
					else {
						$db = JFactory::getDbo();
						if(empty($row->name)){
							$sql = "SELECT des,MIN(id) FROM #__wrd_point WHERE transaction=".$tr[$j]->transaction;
							$db->setQuery($sql);
							$des = $db->loadObjectList();
							$outName =  "WMC GROUP - ".$des[0]->des;
						}
					}

					$activeSheet->setCellValue('L'.$index,$outName);
					$activeSheet->getStyle('L'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$activeSheet->setCellValue('M'.$index,($tr[$j]->point/$total));
					$objPHPExcel->getActiveSheet()->getStyle('M'.$index)->getNumberFormat()->setFormatCode("0.00%");
					$activeSheet->getStyle('M'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$activeSheet->setCellValue('N'.$index,$data[$i]->quantity * $data[$i]->cost_pricing*($tr[$j]->point/$total));
					$activeSheet->getStyle('N'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$objPHPExcel->getActiveSheet()->getStyle('N'.$index)->getNumberFormat()->setFormatCode("#,##0");
					$index++;	
			endif; 
			}		
    }
    unset($data);
}

$fileName = 'redemption_report_product_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          


function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15,$bg="cc99ff")
{
      
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
